﻿using System;
using System.IO;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace HiauPdm.Bll
{
    /// <summary>
    /// 外购件查询
    /// </summary>
    public class BWgjCx
    {
        /// <summary>
        /// 根据[上级外购件类别ID]获得外购件类别
        /// </summary>
        public static DataSet GetChildWgjlb(int iWgjLbId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.编号,A.类别名称,CAST((SELECT COUNT(1) FROM 购件类别 B,购件类别 C WHERE C.ISJID=B.ID AND B.ISJID=A.ID) AS BIT) 含子类别 FROM 购件类别 A WHERE ISNULL(A.ISJID,0)=@P0 ORDER BY A.编号", iWgjLbId);
            }
        }

        /// <summary>
        /// 查询外购件类别
        /// </summary>
        public static DataSet QueryWgjlb(int iSjLbSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.编号,'['+B.类别名称+'] '+A.类别名称 类别名称 FROM 购件类别 A,购件类别 B WHERE A.ISJID=B.ID AND A.ISJID=@P0 ORDER BY A.编号", iSjLbSjId);
            }
        }

        /// <summary>
        /// 查询外购件类别
        /// </summary>
        public static DataSet QueryWgjlb(string sWhere)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT A.ID,A.编号,'['+B.类别名称+'] '+A.类别名称 类别名称 FROM 购件类别 A,购件类别 B WHERE A.ISJID=B.ID AND NOT EXISTS(SELECT 1 FROM 购件类别 C WHERE C.ISJID=A.ID) AND {0} ORDER BY A.编号", BGlobal.BuildLike(sWhere, "A.类别名称", "B.类别名称")));
            }
        }

        /// <summary>
        /// 查询外购件
        /// </summary>
        public static DataSet QueryWgj(string sWhere, string sDh)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT A.ID,A.代号,A.名称,A.规格型号,A.单位,A.品牌厂家 FROM 购件目录 A WHERE (@P0='' OR A.代号 LIKE @P0+'%') AND {0} ORDER BY A.代号", BGlobal.BuildLike(sWhere, "A.代号", "A.名称", "A.规格型号")), sDh);
            }
        }

        /// <summary>
        /// 根据[外购件ID]获得外购件查询
        /// </summary>
        public static DataSet GetWgjCxByWgjId(int iWgjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT ID,代号,类别,名称,规格型号,单位,品牌厂家,备注,编辑人,编辑时间 FROM 购件目录 WHERE ID=@P0", iWgjId);
            }
        }

        /// <summary>
        /// 根据[产品设计ID]查询包含的外购件
        /// </summary>
        public static DataSet GetWgjCxTree(int iCpSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.代号,A.名称,A.规格型号 FROM 购件目录 A, 产品外购 B WHERE A.ID=B.购件ID AND B.产品ID=@P0 ORDER BY A.代号", iCpSjId);
            }
        }

        /// <summary>
        /// 获得外购件查询总记录数
        /// </summary>
        public static DataSet GetWgjCxCount(string sWhere, string sDh)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT COUNT(1) FROM 购件目录 WHERE (@P0='' OR 代号 LIKE @P0+'%') AND {0}", BGlobal.BuildLike(sWhere, "代号", "名称", "规格型号")), sDh);
            }
        }

        /// <summary>
        /// 获得外购件查询
        /// </summary>
        public static DataSet GetWgjCx(string sWhere, string sDh, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.代号,A.名称,A.规格型号,A.单位,A.品牌厂家,A.编辑人,CASE (SELECT COUNT(1) FROM 产品外购 B WHERE B.购件ID=A.ID) WHEN 0 THEN '' ELSE '有' END 引用,(SELECT COUNT(1) FROM 购件文档 B WHERE A.代号 LIKE B.购件代号+'%') 文档 FROM 购件目录 A WHERE (@P0='' OR A.代号 LIKE @P0+'%') AND {2} AND A.ID NOT IN(SELECT TOP {1} B.ID FROM 购件目录 B WHERE (@P0='' OR B.代号 LIKE @P0+'%') AND {3} ORDER BY B.代号) ORDER BY A.代号", iPageSize, iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "A.代号", "A.名称", "A.规格型号"), BGlobal.BuildLike(sWhere, "B.代号", "B.名称", "B.规格型号")), sDh);
            }
        }

        /// <summary>
        /// 导出外购件目录
        /// </summary>
        public static void ExportWgjMl(Page tPage, DataSet dsWgj)
        {
            if (tPage == null || dsWgj == null || dsWgj.Tables[0].Rows.Count <= 0) { return; }
            string sFileName = string.Format("Ls{0}{1}.tmp", tPage.Session.SessionID, DateTime.Now.ToString("yyyyMMddHHmmssffffff"));
            using (DExcel dExcel = new DExcel(tPage.Server.MapPath("~/ExcelMb/WgjMl.xls")))
            {
                if (!dExcel.WriteDataTable(3, 1, dsWgj.Tables[0], "代号", "名称", "规格型号", "单位")) { return; }
                if (!dExcel.OutputExcelFile(string.Format("{0}{1}", BGlobal.AttachmentPath, sFileName))) { return; }
            }
            tPage.Response.Redirect("DownloadFile.aspx?id=" + BGlobal.Encrypt(string.Format("{0}|外购件目录{1}.xls", sFileName, DateTime.Now.ToString("yyyyMMdd")), "Download"));
        }
    }
}
